package gui.dao;

import gui.entity.Category;
import gui.util.DButil;
import javafx.scene.chart.ScatterChart;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CategoryDAO  {
    public int getTotal() throws Exception {
        int Total=0;
        try(Connection conn = DButil.getConnection(); Statement s=conn.createStatement();){
            String sql="select count(*) from category";
            ResultSet rs=s.executeQuery(sql);
            while(rs.next()){
                Total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Total;
    }
  public void add(Category category ) throws Exception {
        String sql="insert into category value(null,?)";
        try(Connection conn=DButil.getConnection(); PreparedStatement ps=conn.prepareStatement(sql)){
            ps.setString(1,category.getName());
            ps.execute();
            ResultSet rs =ps.getGeneratedKeys();
            while(rs.next()) {
                int id = rs.getInt(1);
                category.setId(id);
            }
            }catch(SQLException e){
            e.printStackTrace();
        }
          }
    public void delete(int id) throws Exception{
        String sql="delete category where id = ?";
        try(Connection conn=DButil.getConnection();PreparedStatement ps = conn.prepareStatement(sql)){
            ps.setInt(1,id);
            ps.execute();

        }catch (SQLException e) {

            e.printStackTrace();
        }
    }
    public void update(Category category) throws Exception {
        String sql="update category set name= ? where id=?";
        try(Connection conn = DButil.getConnection();PreparedStatement ps=conn.prepareStatement(sql)){
            ps.setString(1,category.getName());
            ps.setInt(2,category.getId());
            ps.execute();
        }catch (SQLException e) {

            e.printStackTrace();
        }

    }
    public  Category get(int id) throws Exception {
        Category category=null;
        String sql="select * from category where id=?";
        try(Connection conn=DButil.getConnection();PreparedStatement ps=conn.prepareStatement(sql)){
            ResultSet rs=ps.executeQuery();
            if(rs.next()){
                category=new Category();
                String name=rs.getString(2);
                category.setName(name);
                category.setId(id);
            }
        }catch (SQLException e) {

            e.printStackTrace();
        }
        return category;
        }
    public List<Category> list() throws SQLException {
        return list(0,Short.MAX_VALUE);

    }
    public List<Category> list(int start,int count) throws SQLException {
        List<Category> categories=new ArrayList<Category>();
        String sql="select * from category order by id desc limit ?,?";
        try (Connection c = DButil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
            ps.setInt(1,start);
            ps.setInt(2,count);
            ResultSet rs =ps.executeQuery();
            while(rs.next()){
                Category category=new Category();
                category.setId(rs.getInt(1));
                category.setName(rs.getString(2));
                categories.add(category);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return categories;
    }
  }

